﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using MyBasicLib.Reflection;

namespace MyBasicLib.Data.ORMLiteNew.Translation.SqlClient
{
    public sealed class DbQueryProvider : ORMLiteNew.DbQueryProvider
    {
        /// <summary>
        /// table-name ,column-list,value-list 
        /// </summary>
        private const string InsertFormat = "INSERT INTO [{0}] ({1}) VALUES({2})";

        /// <summary>
        /// table-name ,assignment, WHERE expr
        /// </summary>
        private const string UpdateFormat = "UPDATE [t0] SET {1}  FROM [{0}] [t0] {2}";

        /// <summary>
        /// table-name ,WHERE expr
        /// </summary>
        private const string DeleteFormat = "DELETE [t0] FROM [{0}] [t0] {1}";

        private static readonly Regex rgx = new Regex(@"\[t\d+\]\.", RegexOptions.Compiled); //[t0].[Id]取[t0].
       // static string _defaultConn;
      
        //public DbQueryProvider()
        //    : this(new Data.SqlClient.Database(string.Empty), Utilities.IOC.DefaultContainer.Resolve<ILogger>())
        //{
        //}
         public DbQueryProvider(string conn)
            : this(new Data.SqlClient.Database(conn) ) 
        { 
        }
          DbQueryProvider(IDatabase db) 
        { 
            base.Db = db; 
        } 
        #region Helper

        private static string AppendMemberName(StringBuilder sb, Expression expression, TableAliasCache tablecache,
                                               bool appendPre = true)
        {
            int sidx = sb.Length;
            if (expression != null)
            {
                new ExpressionParser().Translate(expression, sb, 0, tablecache);
            }
            string str = sb.ToString(sidx, sb.Length - sidx);
            sb.Length = sidx;
            if (!appendPre) str = rgx.Replace(str, string.Empty);
            sb.Append(str);
            return str;
        }

        private static string AppendWhere(StringBuilder sb, WhereParser whereParser, Expression expression,
                                          TableAliasCache tablecache)
        {
            int sidx = sb.Length;
            if (expression != null)
            {
                sb.Append(" WHERE ");
                whereParser.Translate(expression, sb, whereParser.ParameterValues.Count, tablecache);
            }

            return sb.ToString(sidx, sb.Length - sidx);
        }

        private static string AppendWhere(StringBuilder sb, WhereParser whereParser, Queue<Expression> queue,
                                          TableAliasCache tablecache)
        {
            Expression exp = MergeWhereExpressions(queue);
            ;

            return AppendWhere(sb, whereParser, exp, tablecache);
        }

        private static string AppendOrderBy(StringBuilder sb, Queue<DbExpression> qOrder, TableAliasCache tablecache,
                                            bool appendPre = true)
        {
            int sidx = sb.Length;
            if (qOrder.Count > 0)
            {
                DbExpression exp;
                sb.Append(" ORDER BY ");
                while (qOrder.Count > 0)
                {
                    exp = qOrder.Dequeue();
                    var orExp = exp as IKeySelectorExpression;
                    var cons = orExp.KeySelector as ConstantExpression;
                    if (cons != null)
                    {
                        sb.Append(cons.Value);
                    }
                    else
                        AppendMemberName(sb, orExp.KeySelector, tablecache, appendPre);

                    if (exp.DbExpressionType == DbExpressionType.OrderByDescending ||
                        exp.DbExpressionType == DbExpressionType.ThenByDescending)
                    {
                        sb.Append(" DESC");
                    }
                    sb.Append(",");
                }
                sb.Length--;
            }
            return sb.ToString(sidx, sb.Length - sidx);
        }
        private static string GetJoinTypeString(JoinType joinType)
        {
            switch (joinType)
            {
                case JoinType.InnerJoin:
                    return "INNER JOIN";
                case JoinType.LeftJoin:
                    return "LEFT JOIN";
                case JoinType.CrossJoin:
                    return "CROSS JOIN";
                default:
                    break;
            }
            return string.Empty;
        }
        private static string AppendJoin(StringBuilder sb, Queue<JoinDbExpression> qJoin, TableAliasCache tablecache)
        {
            int sidx = sb.Length;
            if (qJoin.Count > 0)
            {
                while (qJoin.Count > 0)
                {
                    JoinDbExpression exp = qJoin.Dequeue();
                    sb.Append(" " + GetJoinTypeString(exp.JoinType));


                    if (exp.JoinType == JoinType.CrossJoin)
                    {
                        Type intype = exp.InnerKeySelector.Type;

                        sb.AppendFormat(" [{0}] [{1}] ", MetaData.GetTableName(intype),
                                        tablecache.GetTableAlias(exp.InnerKeySelector));
                    }
                    else
                    {
                        Type intype = (exp.Inner).GetType().GetGenericArguments()[0];
                        ;
                        sb.AppendFormat(" [{0}] [{1}] ON ", MetaData.GetTableName(intype),
                                        tablecache.GetTableAlias(exp.InnerKeySelector));

                        AppendMemberName(sb, exp.OuterKeySelector, tablecache);
                        sb.Append("=");
                        AppendMemberName(sb, exp.InnerKeySelector, tablecache);
                    }
                    sb.Append(" ");
                }
                sb.Length--;
            }
            return sb.ToString(sidx, sb.Length - sidx);
        }

        #endregion

        //internal DbQueryProvider(DataContext dc)
        //    : base(dc)
        //{
        //}

        public static string GetUpdateString(Type type, object entity, out IList<object> parameterValues)
        {
            TypeData tdata = TypeDataCache.GetTypeData(type);
            PropertyInfo[] ps = tdata.GetUpdatePropertyInfos();
                //.Where(p=>!p.IsDefined(typeof(DbGeneratedAttribute),false)).ToArray();
            PropertyInfo[] keyps = tdata.GetUpdateKeyPropertyInfos();
                //.Where(p=>!p.IsDefined(typeof(DbGeneratedAttribute),false)).ToArray();
            string sql = tdata.GetUpdateString(type, ps, keyps, GetUpdateString_);
            //GetInsertString_(type, properties);
            IEnumerable<object> p1 = ps.Select(p => p.FastGetValue(entity));
            IEnumerable<object> p2 = keyps.Select(p => p.FastGetValue(entity));
            parameterValues = p1.Concat(p2).ToArray();
            return sql;
        }

        private static string GetUpdateString_(Type type, IList<PropertyInfo> ps, IList<PropertyInfo> keyps)
        {
            var updateActionParser = new UpdateActionParser();
            //  object newValue = entity;
            var sb = new StringBuilder();
            sb.Append("UPDATE [t0] SET ");
            if (ps.Count == 0) return string.Empty;
            if (keyps.Count == 0) return string.Empty;
            foreach (PropertyInfo info in ps)
            {
                //object value = info.FastGetValue(newValue);
                //if (value != null && value.Equals(oldvalue)) continue;
                sb.AppendFormat(CultureInfo.InvariantCulture, "[t0].[{0}] = ", info.Name);
                updateActionParser.HandleParameter(sb, null);
                sb.Append(",");
            }
            --sb.Length;
            sb.Append(" FROM ");
            sb.AppendFormat("[{0}] AS [t0]", MetaData.GetTableName(type));
            sb.Append(" WHERE ");
            foreach (PropertyInfo key in keyps)
            {
                sb.AppendFormat(CultureInfo.InvariantCulture, "[t0].[{0}] = ", key.Name);
                //object keyValue = key.FastGetValue(newValue);
                updateActionParser.HandleParameter(sb, null);
                sb.Append(" AND ");
            }
            sb.Length -= 5; //删除' AND '

            return sb.ToString();
        }

        public   string GetInsertString(Type type, object entity, out IList<object> parameterValues)
        {
            TypeData tdata = TypeDataCache.GetTypeData(type);
            PropertyInfo[] properties = tdata.GetInsertPropertyInfos();
                //.Where(p=>!p.IsDefined(typeof(DbGeneratedAttribute),false)).ToArray();
            string sql = tdata.GetInsertString(type, properties, GetInsertString_);
            parameterValues = properties.Select(p => p.FastGetValue(entity)).ToArray();

            //GetInsertString_(type, properties);
            return sql;
        }

        private   string GetInsertString_(Type type, IList<PropertyInfo> ps)
        { 
            string[] propertyNamesForInsert = ps.
                Select(p => string.Format(CultureInfo.InvariantCulture, "[{0}]", p.Name)).ToArray();
            string columnList = string.Join(",", propertyNamesForInsert);
            string valuelist = string.Join(",", 
                                            GetParameterNamesWithToken(), 0,
                                           propertyNamesForInsert.Length);
            string sql = string.Format(CultureInfo.InvariantCulture,
                                       InsertFormat,
                                       MetaData.GetTableName(type),
                                       columnList, valuelist);
            return sql;
        }

        #region DbQueryProvider

        protected override string HandleSelect(DbQueryInfo queryInfo, out IList<object> parameterValues)
        {
            TableAliasCache tCache = queryInfo.cache;
            var sb = new StringBuilder();
            var selectParser = new SelectParser();
            var whereParser = new WhereParser();
            bool isCount = queryInfo.IsCount;
            sb.Append("SELECT ");
            if (!isCount && queryInfo.IsDistinct)
            {
                sb.Append("DISTINCT ");
            }
            if (!isCount && queryInfo.Take > 0 && queryInfo.Skip == 0)
            {
                sb.Append("TOP (");
                selectParser.HandleParameter(sb, queryInfo.Take);
                sb.Append(") ");
            }
            if (isCount)
            {
                sb.Append("COUNT(*)");
            }
            else
            {
                int idx = sb.Length;
                selectParser.Translate(queryInfo.SelectExpression, sb, selectParser.ParameterValues.Count, tCache);
                sb.Length = idx;
                string sl = string.Join(",",
                                        selectParser.SelectClauses.Select(p => p.SourceName + " AS " + p.AliName).
                                            ToArray());
                sb.Append(sl);
            }
            whereParser.ParameterValues.AddRange(selectParser.ParameterValues);
            sb.Append(" FROM ");
            sb.AppendFormat("[{0}] [t0]", MetaData.GetTableName(queryInfo.SourceType));
            AppendJoin(sb, queryInfo.JoinExpressions, tCache);
            AppendWhere(sb, whereParser, queryInfo.WhereExpression, tCache);
            if (!isCount)
            {
                if (queryInfo.Skip > 0)
                {
                    string selectCols = string.Join(",", selectParser.SelectClauses.Select(p => p.AliName).ToArray());
                    string old = sb.ToString();
                    sb.Length = 0;
                    sb.Append("WITH cet0([ROW_NUMBER]," + selectCols + ") AS (");
                    sb.Append(" SELECT ROW_NUMBER() OVER (");
                    AppendOrderBy(sb, queryInfo.OrderExpressions, tCache, false);
                    sb.Append(") AS [ROW_NUMBER],");
                    sb.Append(selectCols);
                    sb.Append(" FROM (");
                    sb.Append(old);
                    sb.Append(") AS [tmpT1]");
                    sb.Append(")");

                    sb.AppendFormat("SELECT {0} FROM cet0 [tc0] ", selectCols);
                    sb.AppendFormat("WHERE [tc0].[ROW_NUMBER] BETWEEN @p{0} + 1 AND @p{0} + @p{1}",
                                    whereParser.ParameterValues.Count, whereParser.ParameterValues.Count + 1);
                    sb.Append(" ORDER BY [tc0].[ROW_NUMBER]");
                    whereParser.ParameterValues.Add(queryInfo.Skip);
                    whereParser.ParameterValues.Add(queryInfo.Take);
                }
                else AppendOrderBy(sb, queryInfo.OrderExpressions, tCache);
            }
            parameterValues = whereParser.ParameterValues;
            return sb.ToString();
            //  throw new NotImplementedException();
        }

        protected override string HandleDelete(DbQueryInfo queryInfo, out IList<object> parameterValues)
        {
            Type sourceType = queryInfo.SourceType;
            var sb = new StringBuilder();
            var whereParser = new WhereParser();
            TableAliasCache tCache = queryInfo.cache;
            sb.Append("DELETE [t0]");
            sb.Append(" FROM ");
            sb.AppendFormat("[{0}] AS [t0]", MetaData.GetTableName(queryInfo.SourceType));
            AppendJoin(sb, queryInfo.JoinExpressions, tCache);
            AppendWhere(sb, whereParser, queryInfo.WhereExpression, tCache);

            string sqlwhere = sb.ToString();
            parameterValues = whereParser.ParameterValues;
            return sb.ToString();
        }

        protected override string HandleUpdate(DbQueryInfo queryInfo, out IList<object> parameterValues)
        {
            Type type = queryInfo.SourceType;
            var updateExpression = (UpdateDbExpression) queryInfo.UpdateExpression;
            if (updateExpression.ModifyFrom == ModifyFrom.Entity)
            {
                return GetUpdateString(type, updateExpression.Entity, out parameterValues);
            }
            var sb = new StringBuilder();
            var whereParser = new WhereParser();
            TableAliasCache tCache = queryInfo.cache;
            sb.Append("UPDATE [t0] SET ");
            var updateActionParser = new UpdateActionParser();
            int idx = sb.Length;
            updateActionParser.Translate(updateExpression.UpdateExpression, sb, 0, tCache);
            if (sb.Length > idx) //删除','
            {
                sb.Length--;
            }
            whereParser.ParameterValues.AddRange(updateActionParser.ParameterValues);
            sb.Append(" FROM ");
            sb.AppendFormat("[{0}] AS [t0]", MetaData.GetTableName(queryInfo.SourceType));
            AppendJoin(sb, queryInfo.JoinExpressions, tCache);
            AppendWhere(sb, whereParser, queryInfo.WhereExpression, tCache);
            parameterValues = whereParser.ParameterValues;
            return sb.ToString();
        }

        protected override string HandleInsert(DbQueryInfo queryInfo, out IList<object> parameterValues)
        {
            Type type = queryInfo.SourceType;
            InsertDbExpression insertDbExpression = queryInfo.InsertExpression;
            if (insertDbExpression.ModifyFrom == ModifyFrom.Entity)
            {
                string sql = GetInsertString(type, insertDbExpression.Entity, out parameterValues);
                // properties = properties ?? (List<PropertyInfo>)TypeDataCache.GetTypeData(type).GetPropertyInfos().Where(info => !info.IsDefined(typeof(DbGeneratedAttribute), false)).ToList();
                //parameterValues = properties.Select(p => p.FastGetValue(insertDbExpression.Entity)).ToList();
                return sql;
            }
            //INSERT INTO <target_table> SELECT <columns> FROM <source_table>

            var sb = new StringBuilder();
            var selectParser = new SelectParser();
            selectParser.Translate(queryInfo.SelectExpression, sb, 0, queryInfo.cache);
            sb.AppendFormat("INSERT INTO [{0}] ", MetaData.GetTableName(queryInfo.ResultType));

            sb.Append("(");
            string str = string.Join(",", selectParser.SelectClauses.Select(p => p.AliName).ToArray());
            sb.Append(str);
            sb.Append(") ");
            str = HandleSelect(queryInfo, out parameterValues);
            sb.Append(str);
            return sb.ToString();
        }

        #endregion
    }
}